clear all
macro drop _all
set more off
set type double

use qwi_emps_earnings.dta

* Create statecode
order geography
gen strcc = string(geography,"%05.0f")
gen state_fips = substr(strcc,1,2)
destring state_fips, replace
drop strcc
order state_fips geography 

* Cretae quarterly variable
gen date = yq(year, quarter)
format date %tq

/*drop DE from the dataset all-together */
drop if state_fips==10
drop if date>=tq(2017q1)
* Drop the aggregate level we need 2-digit sectors
drop if ind_level =="A" 

* Gen some fixed effects identifiers
egen county_ind_fe = group(geography industry)
egen county_time_fe = group(geography date)
egen ind_time_fe = group(date industry)

/* ban  = 1 for states after they pass the ban */
gen ban = 0

replace ban= 1 if (state_fips==6 & date>=tq(2012q1)) | ///
(state_fips==8 & date>=tq(2013q3)) | (state_fips==09 & date>=tq(2011q4)) | ///
(state_fips==10 & date>=tq(2014q2)) | (state_fips==15 & date>=tq(2009q3)) | ///
(state_fips==17 & date>=tq(2011q1)) | (state_fips==24 & date>=tq(2011q4)) | ///
(state_fips==32 & date>=tq(2013q4)) | (state_fips==41 & date>=tq(2010q2)) | ///
(state_fips==50 & date>=tq(2012q3)) | (state_fips==53 & date>=tq(2007q3))

gen exempt = 0
replace exempt = 1 if (industry=="52" | industry == "99" | industry=="55" )

gen ban_affected = ban*(1-exempt)

* Earnings REgression Note drop if data is fuzzed
keep if searns == 1
gen log_earn = log(earns)

preserve
drop if (firmage ==1 )

reghdfe log_earn ban_affected, absorb(county_ind_fe county_time_fe ind_time_fe) vce(cluster state_fips industry)
outreg2 using table_A9.xls, dec(3)  replace

restore

** QWI SHARE OF EMPLOYMENT by YOUNG FIRMS -  Second Column in Table A9

preserve
gen newf_emp = 0
replace newf_emp = emps if firmage ==1

gen old_emp =0
replace old_emp = emps if firmage ==0
 
collapse (sum) old_emp newf_emp (mean) ban_affected county_ind_fe county_time_fe ind_time_fe state_fips, by (date geography industry)
 
gen new_emp_sh = 100*(newf_emp/old_emp)
centile new_emp_sh, centile(10(10)100)

reghdfe new_emp_sh ban_affected, absorb(county_ind_fe county_time_fe ind_time_fe) vce(cluster state_fips industry)
outreg2 using table_A9.xls, dec(3) append

restore
clear

** USE Deposit Data for the last two set of regressions in Table A9
use FDIC_Deposits_2005_2016.dta

gen strcc = string(statecountycode,"%05.0f")
gen statecode = substr(strcc,1,2)

destring statecode, replace

* get back to the same sample
/*drop DE from the dataset all-together */
drop if statecode==10
drop if year>=2017
/* ban  = 1 for states after they pass the ban, only for non-exempt occupations */
gen ban = 0

replace ban= 1 if (statecode==6 & year>=2012) | ///
(statecode==8 & year>=2013) | (statecode==09 & year>=2011) | ///
(statecode==10 & year>=2014) | (statecode==15 & year>=2009) | ///
(statecode==17 & year>=2011) | (statecode==24 & year>=2011) | ///
(statecode==32 & year>=2013) | (statecode==41 & year>=2010) | ///
(statecode==50 & year>=2012) | (statecode==53 & year>=2007)

gen log_avgd = log(average_deposits)
gen log_totd = log(total_deposits)
 
reghdfe log_avgd ban, absorb(statecountycode year) vce(cluster statecode)
outreg2 using table_A9.xls, dec(3) append

reghdfe log_totd ban, absorb(statecountycode year) vce(cluster statecode)
outreg2 using table_A9.xls, dec(3) append

